SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE FUNCTION dbo.[DecodeURL]
(
	@URL NVARCHAR(MAX)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
	DECLARE @Position INT,
		@Base CHAR(16),
		@High TINYINT,
		@Low TINYINT,
		@Pattern CHAR(21)

	SELECT	@Base = '0123456789abcdef',
		@Pattern = '%[%][0-9a-f][0-9a-f]%',
		@URL = REPLACE(@URL, '+', ' '),
		@Position = PATINDEX(@Pattern, @URL)

	WHILE @Position > 0
		SELECT	@High = CHARINDEX(SUBSTRING(@URL, @Position + 1, 1), @Base),
			@Low = CHARINDEX(SUBSTRING(@URL, @Position + 2, 1), @Base),
			@URL = STUFF(@URL, @Position, 3, CHAR(16 * @High + @Low - 17)),
			@Position = PATINDEX(@Pattern, @URL)

		RETURN	@URL
END

GO
